﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Web.Security;
using System.Configuration.Provider;
using System.Data;
using System.Data.Common;
using Npgsql;

namespace AllMyMoney.RoleProviders
{
    internal sealed class PostgresRoleProvider : RoleProvider
    {
        //
        // System.Web.Security.RoleProvider properties.
        //
        public override string ApplicationName
        {
            get {return _applicationName;}
            set {_applicationName = value;}
        }
        //
        // System.Configuration.Provider.ProviderBase.Initialize Method
        //        
        public override void Initialize(string name, NameValueCollection config)
        {
            base.Initialize(name, config);

            if (config == null)
                throw new System.ArgumentNullException("config");

            // Database config
            _databaseFactoryName = config["databaseFactory"];
            _databaseConnectionString = config["databaseConnectionString"];

            if (_databaseFactoryName == null)
                throw new ProviderException("Failed to locate a databaseFactory parameter in config");

            if (_databaseConnectionString == null)
                throw new ProviderException("Failed to locate a databaseConnectionString parameter in config");

            _databaseFactory = DbProviderFactories.GetFactory(_databaseFactoryName);

            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            // Reading application name from database config table
            // Load config from database
            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT * FROM config WHERE id=:applicationNameId";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":applicationNameId", DbType.String));
                command.Parameters["applicationNameId"].Value = "applicationName";

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                        throw new ProviderException("Failed to locate application name in config");

                    int paramValueOrdinal = reader.GetOrdinal("name");
                    _applicationName = reader.GetString(paramValueOrdinal);
                }
            }
        }

        //
        // RoleProvider.AddUsersToRoles
        //
        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                NpgsqlTransaction transaction = (databaseConnection as NpgsqlConnection) .BeginTransaction();

                foreach (string username in usernames)
                {
                    foreach (string rolename in roleNames)
                    {
                        DbCommand command = databaseConnection.CreateCommand();
                        command.CommandText = "INSERT INTO \"usersInRoles\" VALUES (:username,:rolename)";

                        command.Parameters.Add(new NpgsqlParameter(":username", DbType.String));
                        command.Parameters.Add(new NpgsqlParameter(":rolename", DbType.String));

                        command.Parameters["username"].Value = username;
                        command.Parameters["rolename"].Value = rolename;

                        try
                        {
                            transaction.Save("beforeInsert");
                            command.ExecuteNonQuery();
                        }
                        catch (NpgsqlException exc)
                        {
                            // Switch according to PostreSQL error codes
                            switch (exc.Code)
                            {
                                case "23505":
                                    // User is already in role
                                    transaction.Rollback("beforeInsert");
                                    continue;
                                case "23503":
                                    throw new ProviderException("User " + username + " or role " + rolename + " does not exist");
                                default:
                                    throw;
                            }
                        }
                    }
                }
                transaction.Commit();
            }
        }

        //
        // RoleProvider.CreateRole
        //
        public override void CreateRole(string roleName)
        {
            if (roleName == null)
                throw new System.ArgumentNullException("roleName is null");

            if ( (roleName=="") || (roleName.Contains(",")) )
                throw new System.ArgumentNullException("roleName is empty string or contain a comma");

            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = "INSERT INTO roles VALUES (:roleName,:roleDescription)";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":roleName", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":roleDescription", DbType.String));

                command.Parameters["roleName"].Value = roleName;
                command.Parameters["roleDescription"].Value = "N/A";

                try
                {
                    command.ExecuteNonQuery();
                }
                catch (NpgsqlException exc)
                {
                    // Switch according to PostreSQL error codes
                    switch (exc.Code)
                    {
                        case "23505":
                            throw new ProviderException("Role already exists");
                        default:
                            throw new ProviderException("Unknown error",exc);
                    }
                }
            }
        }

        //
        // RoleProvider.DeleteRole
        //
        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            if (roleName == null)
                throw new System.ArgumentNullException("roleName is null");

            if ((roleName == "") || (roleName.Contains(",")))
                throw new System.ArgumentNullException("roleName is empty string or contain a comma");

            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                System.Text.StringBuilder queryBuilder = new System.Text.StringBuilder();

                if (!throwOnPopulatedRole)
                    queryBuilder.AppendFormat("DELETE FROM \"usersInRoles\" WHERE roleName=:roleName;");
                queryBuilder.AppendFormat("DELETE FROM roles WHERE roleName=:roleName;");
               
                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = queryBuilder.ToString ();
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":roleName", DbType.String));
                command.Parameters["roleName"].Value = roleName;

                try
                {
                    command.ExecuteNonQuery();
                }
                catch (NpgsqlException exc)
                {
                    switch (exc.Code)
                    {
                        case "23503":
                            throw new ProviderException("Role is in use");
                        default:
                            throw;
                    }
                }
            }

            return true;
        }

        //
        // RoleProvider.GetAllRoles
        //
        public override string[] GetAllRoles()
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = "SELECT roleName FROM roles";
                command.CommandType = CommandType.Text;

                using (DbDataReader reader = command.ExecuteReader())
                    return GetFirstColumnFromReaderAsString (reader);
            }
        }

        //
        // RoleProvider.GetRolesForUser
        //
        public override string[] GetRolesForUser(string username)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = "SELECT roleName FROM \"usersInRoles\" WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":username",DbType.String));
                command.Parameters["username"].Value = username;

                using (DbDataReader reader = command.ExecuteReader())
                    return GetFirstColumnFromReaderAsString(reader);
            }
        }

        //
        // RoleProvider.GetUsersInRole
        //
        public override string[] GetUsersInRole(string roleName)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = "SELECT userName FROM \"usersInRoles\" WHERE roleName=:roleName";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":roleName", DbType.String));
                command.Parameters["roleName"].Value = roleName;

                using (DbDataReader reader = command.ExecuteReader())
                    return GetFirstColumnFromReaderAsString(reader);
            }
        }

        //
        // RoleProvider.IsUserInRole
        //
        public override bool IsUserInRole(string userName, string roleName)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = "SELECT roleName FROM \"usersInRoles\" WHERE userName=:userName AND roleName=:roleName";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":userName", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":roleName", DbType.String));

                command.Parameters["userName"].Value = userName;
                command.Parameters["roleName"].Value = roleName;

                using (DbDataReader reader = command.ExecuteReader())
                    return reader.Read();
            }
        }

        //
        // RoleProvider.RemoveUsersFromRoles
        //
        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                foreach (string username in usernames)
                    foreach (string rolename in roleNames)
                    {
                        DbCommand command = databaseConnection.CreateCommand();

                        command.CommandText = "DELETE FROM \"usersInRoles\" WHERE userName=:userName AND roleName=:roleName";
                        command.CommandType = CommandType.Text;

                        command.Parameters.Add(new NpgsqlParameter(":userName", DbType.String));
                        command.Parameters.Add(new NpgsqlParameter(":roleName", DbType.String));

                        command.Parameters["userName"].Value = username;
                        command.Parameters["roleName"].Value = rolename;

                        command.ExecuteNonQuery();
                    }
            }
        }

        //
        // RoleProvider.RoleExists
        //
        public override bool RoleExists(string roleName)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = "SELECT roleName FROM roles WHERE roleName=:roleName";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":roleName", DbType.String));
                command.Parameters["roleName"].Value = roleName;

                using (DbDataReader reader = command.ExecuteReader())
                    return reader.Read();
            }
        }

        //
        // RoleProvider.FindUsersInRole
        //
        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = "SELECT userName FROM \"usersInRoles\" WHERE roleName=:roleName AND userName LIKE :usernameToMatch";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":roleName", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":usernameToMatch", DbType.String));

                command.Parameters["roleName"].Value = roleName;
                command.Parameters["usernameToMatch"].Value = "%"+usernameToMatch+"%";

                using (DbDataReader reader = command.ExecuteReader())
                    return GetFirstColumnFromReaderAsString(reader);
            }
        }

        //
        // Helper method to extract role names from reader to string array
        //
        private string [] GetFirstColumnFromReaderAsString (DbDataReader reader)
        {
            List<string> values = new List<string>();

            while (reader.Read())
                values.Add(reader.GetString(0));
    
            string[] result = new string[values.Count];
            for (int idx = 0; idx < values.Count; idx++)
                result[idx] = values[idx];

            return result;
        }


        private string _databaseFactoryName;
        private string _databaseConnectionString;
        private DbProviderFactory _databaseFactory;
        
        private string _applicationName;
    }
}